


ALTER   function fn_get_lot_reserved_quantity (@mktg_part_number varchar(50))
returns  @fn_results Table (ProcessStep_Name varchar(50), 
			lot_quantity int, 
			expected_date_out smalldatetime,
			lot_number varchar(50),
			lot_status varchar(50))
as 
begin
	declare @expected_date_out SMALLDATETIME
	declare @lot_Number varchar(50)
	declare @ps_type_name varchar(20)
	declare @product_id int
	declare @process_step_id int
	declare @line_item_no int
	declare @sales_order_id int
	declare @lot_quantity int
	declare @lot_status varchar(50)

	declare @results Table (ProcessStep_Name varchar(50), lot_quantity int, expected_date_out smalldatetime, lot_number varchar(50), lot_status varchar(50))
	select @product_id = Product_ID from Product where P_Marketing_Part_Number = @mktg_part_number
	
	declare lineItemCursor CURSOR FOR
		select COOL_Line_Item_No, SalesOrder_ID 
			FROM Customer_Open_Order_LineItem WHERE Product_ID = @product_id
		
	open lineItemCursor 
		fetch next from lineItemCursor into @line_item_no,@sales_order_id
		while @@fetch_status = 0
		begin
			declare lotCursor CURSOR FOR
			select LRT_Lot_Number, LRT_Lot_Quantity, LRT_Expected_Date_Out
				from Lot_Reservation_Table where LRT_Line_Item_No = @line_item_no
				and LRT_SalesOrder_Id = @sales_order_id and LRT_Released = 1

			open lotCursor 
				fetch next from lotCursor into @lot_number, @lot_quantity, @expected_date_out				
				while @@fetch_status = 0
				begin			
					select @ps_type_name = Ps_type_name, @lot_status = Lot_Current_Status from Lot_Table,Process_Step WHERE Lot_Number= @lot_number and Lot_Current_Status='Running' and Lot_Table.ProcessStep_ID = Process_Step.ProcessStep_ID
					insert into @results (ProcessStep_Name, lot_quantity, expected_date_out, lot_number, lot_status) values (@ps_type_name, @lot_quantity, @expected_date_out, @lot_number, @lot_status)
					fetch next from lotCursor into  @lot_number,@lot_quantity, @expected_date_out
				end
			close lotCursor
			deallocate lotCursor
			fetch next from lineItemCursor into @line_item_no,@sales_order_id
		end
	close lineItemCursor
	deallocate lineItemCursor
	insert @fn_results select *from @results
	return
end



